from django.db import connection, IntegrityError

from libs.form import Page


def exec_insert(cursor, sql, binding_arr):
    rowid = 0
    err = None
    affected = cursor.execute(sql, binding_arr)
    if affected != 1:
        err = '插入数据失败'
        return rowid, err
    rowid = cursor.lastrowid
    return rowid, err


def escape_field(field, as_name=''):
    if as_name == '':
        return '`%s`' % field
    else:
        return '%s.`%s`' % (as_name, field)


def escape_fields(fields: list, as_name=''):
    arr = []
    for field in fields:
        arr.append(escape_field(field, as_name))
    return arr


def process_field_list(field_list, table_as=''):
    field_arr = []
    query_field_arr = []
    if table_as != '':
        table_as += '.'
    for field in field_list:
        field_typ = type(field)
        if field_typ is str:
            field_arr.append(field)
            query_field_arr.append('{}`{}`'.format(table_as, field))
        elif field_typ is tuple:
            field_num = len(field)
            if field_num == 2:
                k, v = field
                field_arr.append(v)
                query_field_arr.append('{}`{}` as `{}`'.format(table_as, k, v))
            elif field_num == 3:
                k, v, func = field
                field_arr.append(v)
                query_field_arr.append('{}({}`{}`) as `{}`'.format(func, table_as, k, v))
    return field_arr, query_field_arr


def dict_fetchall(cursor):
    columns = [col[0] for col in cursor.description]
    rows = cursor.fetchall()
    return [
        dict(zip(columns, row))
        for row in rows
    ]


class DB:
    table_name = ''
    table_as = ''
    conn = None
    data_dict = {}
    fields = []
    wheres = []
    binding_arr = []
    is_duplicate = False
    field_str = ''
    group_by = ''
    order = ''
    limit_str = ''
    where_str = ''
    left_join_str = ''

    def __init__(self, table_name, conn=None, table_as=''):
        self.init_query()
        self.table_name = table_name
        if table_as != '':
            self.table_as = ' ' + table_as
        self.conn = conn

    def init_query(self):
        self.field_str = ''
        self.left_join_str = ''
        self.group_by = ''
        self.order = ''
        self.limit_str = ''
        self.where_str = ''
        self.data_dict = {}
        self.fields = []
        self.wheres = []
        self.binding_arr = []

    def build_select_sql(self):
        cond_dict = {
            'table_as': self.table_as,
            'field_str': self.field_str,
            'left_join_str': self.left_join_str,
            'table_name': self.table_name,
            'where_str': self.where_str,
            'group_by': self.group_by,
            'order': self.order,
            'limit_str': self.limit_str,
        }
        return 'select {field_str} from `{table_name}`{table_as}{left_join_str}' \
               '{where_str}' \
               '{group_by}' \
               '{order}' \
               '{limit_str}'.format(**cond_dict)

    def build_insert_sql(self):
        table_name = self.table_name
        data_dict = self.data_dict
        fields = []
        placeholders = []
        binding_arr = []
        for key in data_dict:
            val = data_dict.get(key, '')
            fields.append('`{}`'.format(key))
            placeholders.append('%s')
            binding_arr.append(val)
        field_str = ','.join(fields)
        placeholder_str = ','.join(placeholders)
        cond_dict = {
            'table_name': table_name,
            'field_str': field_str,
            'placeholder_str': placeholder_str
        }
        sql = 'insert into `{table_name}` ({field_str}) values ({placeholder_str})'.format(**cond_dict)
        return sql, binding_arr

    def build_update_sql(self):
        binding_arr = []
        update_fields = []
        data_dict = self.data_dict
        for key in data_dict:
            val = data_dict.get(key, '')
            update_fields.append('`{}`=%s'.format(key))
            binding_arr.append(val)
        set_str = ' set ' + ','.join(update_fields)
        binding_arr = binding_arr + self.binding_arr
        cond_dict = {
            'set_str': set_str,
            'table_name': self.table_name,
            'where_str': self.where_str,
        }
        sql = 'update `{table_name}`{set_str}{where_str}'.format(**cond_dict)
        return sql, binding_arr

    def build_delete_sql(self):
        cond_dict = {
            'table_name': self.table_name,
            'where_str': self.where_str,
        }
        sql = 'delete from `{table_name}`{where_str}'.format(**cond_dict)
        return sql

    def handle_fields(self, fields):
        fields_typ = type(fields)
        if fields_typ is list:
            field_arr, query_field_arr = process_field_list(fields)
            self.fields = field_arr
            self.field_str = ','.join(query_field_arr)
        elif fields_typ is dict:
            field_arr = []
            query_field_arr = []
            for table_as in fields:
                table_as_fields = fields.get(table_as)
                _field_arr, _query_field_arr = process_field_list(table_as_fields, table_as)
                field_arr += _field_arr
                query_field_arr += _query_field_arr
            self.fields = field_arr
            self.field_str += ','.join(query_field_arr)
        elif fields_typ is str:
            if fields == '*':
                self.field_str = fields
            else:
                self.fields = ['_field']
                self.field_str = fields + ' _field'

    def handle_wheres(self, wheres, binding_arr):
        wheres_typ = type(wheres)
        if wheres_typ is dict:
            for field in wheres:
                val = wheres[field]
                if val is None:
                    self.wheres.append(field)
                else:
                    val_typ = type(val)
                    if val_typ is dict:
                        for fk in val:
                            fv = val.get(fk)
                            if fv is None:
                                self.wheres.append('{}.{}'.format(field, fk))
                            else:
                                self.wheres.append('{}.`{}`'.format(field, fk) + '=%s')
                                self.binding_arr.append(fv)
                    else:
                        self.wheres.append(escape_field(field) + '=%s')
                        self.binding_arr.append(val)
        elif wheres_typ is str:
            if wheres != '':
                self.wheres.append(wheres)
        if type(binding_arr) is list and len(binding_arr) > 0:
            self.binding_arr += binding_arr
        if len(self.wheres) > 0:
            self.where_str = ' where ' + ' and '.join(self.wheres)
        return self.where_str

    def one(self, fields, where='', binding_arr=None):
        if binding_arr is None:
            binding_arr = []
        data = {}
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            self.handle_fields(fields)
            self.handle_wheres(where, binding_arr)
            self.limit_str = ' limit 1'
            sql = self.build_select_sql()
            conn.execute(sql, self.binding_arr)
            if fields == '*':
                rows = dict_fetchall(conn)
                if len(rows) > 0:
                    data = rows[0]
            else:
                row = conn.fetchone()
                if row is not None:
                    for i, field in enumerate(self.fields):
                        data[field] = row[i]
        except Exception as e:
            print(e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return data

    # {'m': {'table': 'member', 'on': 'm.id=b.id'}}
    def left_join(self, join_table_dict):
        left_join_arr = []
        for table_as in join_table_dict:
            join_table = join_table_dict.get(table_as)
            table = join_table.get('table')
            on = join_table.get('on')
            join_str = ' left join `{}` {} on {}'.format(table, table_as, on)
            left_join_arr.append(join_str)
        self.left_join_str = ''.join(left_join_arr)
        return self

    def query(self, sql, binding_arr=None):
        if binding_arr is None:
            binding_arr = []
        rows = []
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            conn.execute(sql, binding_arr)
            rows = dict_fetchall(conn)
        except Exception as e:
            print(e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return rows

    def page(self, page: Page):
        self.limit_str = page.get_limit()
        return self

    def order_by(self, order_by):
        self.order = ' order by ' + order_by
        return self

    def count(self, where='', binding_arr=None):
        if binding_arr is None:
            binding_arr = []
        total = 0
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            self.field_str = 'count(*) _count'
            self.handle_wheres(where, binding_arr)
            sql = self.build_select_sql()
            conn.execute(sql, self.binding_arr)
            row = conn.fetchone()
            if row is not None:
                total = row[0]
        except Exception as e:
            print(e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return total

    def find(self, fields, where='', binding_arr=None):
        if binding_arr is None:
            binding_arr = []
        arr = []
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            self.handle_fields(fields)
            self.handle_wheres(where, binding_arr)
            sql = self.build_select_sql()
            if len(self.binding_arr) == 0:
                self.binding_arr = None
            conn.execute(sql, self.binding_arr)
            if fields == '*':
                arr = dict_fetchall(conn)
            else:
                rows = conn.fetchall()
                for row in rows:
                    data = {}
                    for i, field in enumerate(self.fields):
                        data[field] = row[i]
                    arr.append(data)
        except Exception as e:
            print(e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return arr

    def insert(self, data, update_data=None):
        err = None
        rowid = 0
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            self.data_dict = data
            sql, binding_arr = self.build_insert_sql()
            if update_data is not None:
                set_arr = []
                for key in update_data:
                    val = update_data.get(key, '')
                    set_arr.append('`{}`=%s'.format(key))
                    binding_arr.append(val)
                if len(set_arr) > 0:
                    set_sql = ','.join(set_arr)
                    sql += ' on duplicate key update set ' + set_sql
            rowid, err = exec_insert(conn, sql, binding_arr)
        except IntegrityError as e:
            if e.args[0] == 1062:
                err = '数据已存在'
                self.is_duplicate = True
        except Exception as e:
            err = '插入数据异常'
            print(err, e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return rowid, err

    def update(self, data, where='', binding_arr=None):
        if binding_arr is None:
            binding_arr = []
        err = None
        affected = 0
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            self.data_dict = data
            self.handle_wheres(where, binding_arr)
            sql, binding_arr = self.build_update_sql()
            affected = conn.execute(sql, binding_arr)
        except IntegrityError as e:
            if e.args[0] == 1062:
                err = '数据已存在'
                self.is_duplicate = True
        except Exception as e:
            err = '更新数据异常'
            print(err, e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return affected, err

    def val(self, fields, where='', binding_arr=None):
        if type(fields) is list and len(fields) > 0:
            fields = fields[0]
        row = self.one(fields, where, binding_arr)
        return row.get('_field', None)

    def delete(self, where='', binding_arr=None):
        if binding_arr is None:
            binding_arr = []
        err = None
        affected = 0
        auto_create_conn = False
        if self.conn is None:
            auto_create_conn = True
            self.conn = connection.cursor()
        conn = self.conn
        try:
            where_str = self.handle_wheres(where, binding_arr)
            if where_str == '':
                return 0, '缺少删除条件'
            sql = self.build_delete_sql()
            affected = conn.execute(sql, self.binding_arr)
        except Exception as e:
            err = '删除数据异常'
            print(err, e)
        finally:
            if auto_create_conn:
                conn.close()
            self.init_query()
        return affected, err
